****Appalachian Regional Comission
clear all
cls
capture log close // Closes any open logs
version 15 // Tells STATA what version to use

*Begin Assignmment
global dir "/Users/DanielBFiroozi/Documents/Research/Appalachia Regional Commission/"
log using "$dir/log/datacleanup", replace

set matsize 11000
set maxvar 30000

est clear

*Clean All Industry Data
{
forvalues i= 2006(1)2019 {
global loopyear "`i'"

import delimited "$dir/all industries/$loopyear.q1-q4 10 Total, all industries.csv"
destring area_fips, replace force
drop if area_fips==.
keep if agglvl_code==70 | agglvl_code==71

rename month1_emplvl employment1
rename month2_emplvl employment2
rename month3_emplvl employment3

keep area_fips area_title own_code year qtr employment1 employment2 employment3 qtrly_estabs_count avg_wkly_wage
reshape long employment, i(area_fips year qtr own_code  qtrly_estabs_count avg_wkly_wage area_title) j(month)
reshape wide employment qtrly_estabs_count avg_wkly_wage, i(area_fips year qtr month area_title ) j(own_code)
order area_fips year qtr month

save "$dir/all industries/$loopyear.dta", replace
clear all
}

use "$dir/all industries/2006.dta"

forvalues i= 2007(1)2019 {
global loopyear "`i'"
append using "$dir/all industries/$loopyear.dta"
}

gen qtrly_estabs_count8=qtrly_estabs_count1+qtrly_estabs_count2+qtrly_estabs_count3
gen avg_wkly_wage8=(avg_wkly_wage1*employment1+avg_wkly_wage2*employment2+avg_wkly_wage3*employment3)/(employment1+employment2+employment3)
gen employment8=employment1+employment2+employment3


forvalues i= 0(1)8 {
global loop `i'
capture rename qtrly_estabs_count`i' firms`i'
capture label var firms`i' "Total Firms"
capture rename avg_wkly_wage`i' wage`i'
capture label var wage`i' "Nominal Average Weekly Wage"
capture rename employment`i' jobs`i'
capture label var jobs`i' "Employment"
}

gen fiscalyear=year
replace fiscalyear=year+1 if qtr==4
order area_fips year fiscalyear qtr month
rename area_fips fipscode

save "$dir/all industries/allindustries.dta", replace

clear all
}
*Clean Census Population Data
{
import delimited "$dir/censusdata/cc-est2018-alldata.csv", clear
keep if year>=3 & year<=11
replace year=2010+year-3
keep if agegrp>=4 & agegrp<=13
gen fipscode=state*1000+county
order fipscode year
collapse (first) stname ctyname (sum)tot_pop, by(fipscode year)

save "$dir/censusdata/2010-2018.dta", replace
clear all


import delimited "$dir/censusdata/co-est00int-alldata-01.csv"
save "$dir/censusdata/state1.dta", replace
clear all

forvalues i= 13(1)54 {
global loopyear "`i'"
capture import delimited "$dir/censusdata/co-est00int-alldata-$loopyear.csv"
capture save "$dir/censusdata/state$loopyear.dta", replace
clear all
}

use "$dir/censusdata/state1.dta"

forvalues i= 13(1)54 {
global loopyear "`i'"
capture append using "$dir/censusdata/state$loopyear.dta"
}

keep if year>=2 & year<=11
replace year=2000+year-2
keep if agegrp>=4 & agegrp<=13
gen fipscode=state*1000+county
order fipscode year
collapse (first) stname ctyname (sum)tot_pop, by(fipscode year)

save "$dir/censusdata/2000-2009.dta", replace

append using "$dir/censusdata/2010-2018.dta"

keep fipscode-tot_pop
rename tot_pop working_age_pop

save "$dir/censusdata/WorkingAgePop.dta", replace

clear all

*Repeat but this time for the entire adult population 
import delimited "$dir/censusdata/cc-est2018-alldata.csv"
keep if year>=3 & year<=11
replace year=2010+year-3
keep if agegrp>=4 & agegrp<=18
gen fipscode=state*1000+county
order fipscode year
collapse (first) stname ctyname (sum)tot_pop, by(fipscode year)

save "$dir/censusdata/2010-2018.dta", replace
clear all


import delimited "$dir/censusdata/co-est00int-alldata-01.csv"
save "$dir/censusdata/state1.dta", replace
clear all

forvalues i= 13(1)54 {
global loopyear "`i'"
capture import delimited "$dir/censusdata/co-est00int-alldata-$loopyear.csv"
capture save "$dir/censusdata/state$loopyear.dta", replace
clear all
}

use "$dir/censusdata/state1.dta"

forvalues i= 13(1)54 {
global loopyear "`i'"
capture append using "$dir/censusdata/state$loopyear.dta"
}

keep if year>=2 & year<=11
replace year=2000+year-2
keep if agegrp>=4 & agegrp<=18
gen fipscode=state*1000+county
order fipscode year
collapse (first) stname ctyname (sum)tot_pop, by(fipscode year)

save "$dir/censusdata/2000-2009.dta", replace

append using "$dir/censusdata/2010-2018.dta"


rename tot_pop adult_pop

save "$dir/censusdata/2000-2018.dta", replace

clear all
}
*Rank Index
{
clear all
forvalues i= 2007(1)2008 {
global loopyear "`i'"
global endtail "`i'_Data"
import excel "$dir/rank index/County-Economic-Status_FY$endtail.xls", sheet("ARC Counties") cellrange(A4:N415) firstrow
gen fiscalyear=`i'
rename CountyEconomicStatusFY`i' category
rename CompositeIndexValueFY`i' compositeindex
rename IndexValueRankof3110count rankindex
rename ThreeYearAverageUnemployment urate
rename PerCapitaMarketIncome income
rename PovertyRate2 prate
rename ThreeYearAvgUnempRatePerc urate_scaled
rename PCMIPercentofUSInversed income_scaled
rename PovertyRatePercentofU prate_scaled
keep FIPS-category urate income prate urate_scaled income_scaled prate_scaled compositeindex rankindex fiscalyear
save "$dir/rank index/$loopyear.dta", replace
clear all

}

forvalues i= 2009(1)2016 {
global loopyear "`i'"
global endtail "`i'_Data"
import excel "$dir/rank index/County-Economic-Status_FY$endtail.xls", sheet("ARC Counties") cellrange(A4:N425) firstrow
gen fiscalyear=`i'
rename CountyEconomicStatusFY`i' category
rename CompositeIndexValueFY`i' compositeindex
rename IndexValueRankof3110count rankindex
rename ThreeYearAverageUnemployment urate
rename PerCapitaMarketIncome income
rename PovertyRate2 prate
rename ThreeYearAvgUnempRatePerc urate_scaled
rename PCMIPercentofUSInversed income_scaled
rename PovertyRatePercentofU prate_scaled
keep FIPS-category urate income prate urate_scaled income_scaled prate_scaled compositeindex rankindex fiscalyear
save "$dir/rank index/$loopyear.dta", replace
clear all

}

forvalues i= 2017(1)2020 {
global loopyear "`i'"
global endtail "`i'_Data"
import excel "$dir/rank index/County-Economic-Status_FY$endtail.xls", sheet("ARC Counties") cellrange(A5:N426) firstrow
gen fiscalyear=`i'
rename CountyEconomicStatusFY`i' category
rename CompositeIndexValueFY`i' compositeindex
rename IndexValueRankof3113count rankindex
rename ThreeYearAverageUnemployment urate
rename PerCapitaMarketIncome income
rename PovertyRate2 prate
rename ThreeYearAvgUnempRatePerc urate_scaled
rename PCMIPercentofUSInversed income_scaled
rename PovertyRatePercentofU prate_scaled
keep FIPS-category urate income prate urate_scaled income_scaled prate_scaled compositeindex rankindex fiscalyear
save "$dir/rank index/$loopyear.dta", replace
clear all

}



use "$dir/rank index/2007.dta"

forvalues i= 2008(1)2020 {
global loopyear "`i'"
capture append using "$dir/rank index/$loopyear.dta"
}

destring FIPS, replace force
drop if FIPS==.
rename FIPS fipscode

save "$dir/rank index/rankindex.dta", replace

clear all
}
*Presidential Election Data
{
import delimited "$dir/election data/countypres_2000-2016.csv", clear 
destring fips, replace force
rename fips fipscode
keep if fipscode!=.
rename year electionyear
gen ballot=1 if party=="democrat"
replace ballot=2 if party=="republican"
replace ballot=3 if party!="democrat" & party!="republican"
destring candidatevotes, replace force
collapse (sum) candidatevotes (first) totalvotes, by(fipscode electionyear ballot)
reshape wide candidatevotes, i(electionyear fipscode totalvotes) j(ballot)
rename candidatevotes1 democrat
rename candidatevotes2 republican
rename candidatevotes3 thirdparty
gen dem=democrat/totalvotes
gen rep=republican/totalvotes
gen ind=thirdparty/totalvotes

gen d_2party= democrat/(democrat+republican)

save "$dir/election data/presidential_elections.dta", replace

clear all

import excel "$dir/election data/Pres_Election_Data_1996.xlsx", sheet("County") cellrange(A2:BJ3465) firstrow

gen d_2party1996=N/(N+O)
destring BA, gen(fipscode) 
drop if fipscode==. | fipscode<1000
keep fipscode d_2party1996
drop if d_2party1996==.

save "$dir/election data/presidential_elections1996.dta", replace
clear all


}
*FRED CPI Data
{
import delimited "$dir/FREDCPI/CPALTT01USQ661S.csv", clear 
rename cpaltt01usq661s cpi
replace cpi=107.646/cpi
gen year=substr(date,1,4)
destring year, replace
gen qtr=substr(date,6,2)
destring qtr, replace 
replace qtr=2 if qtr==4
replace qtr=3 if qtr==7
replace qtr=4 if qtr==10
drop date
save "$dir/FREDCPI/CPI.dta", replace

clear all
}
*Education Data
{
capture import excel "$dir/education/College_Education_2000.xls", sheet("ARC Counties") ///
cellrange(A3:J423) firstrow clear
gen edyear=2000
rename PercentCompletedHighS hsgradrate
rename PercentCompletedCollege bachrate
rename HighSchoolCompletionPercent hs_scaled
rename CollegeCompletionPercentofU coll_scaled
rename FIPS fipscode
destring fipscode, replace force
keep fipscode edyear hsgradrate bachrate hs_scaled coll_scaled
capture save "$dir/education/2000.dta", replace
clear all

forvalues i= 2009(1)2017 {
global loopyear "`i'"
capture import excel "$dir/education/College_Education_$loopyear.xls", sheet("ARC Counties") ///
cellrange(A3:J423) firstrow clear
gen edyear=`i'
rename PercentCompletedHighS hsgradrate
rename PercentCompletedBachelorsDeg bachrate
rename HighSchoolCompletionPercent hs_scaled
rename CollegeCompletionPercentofU coll_scaled
rename FIPS fipscode
destring fipscode, replace force
keep fipscode edyear hsgradrate bachrate hs_scaled coll_scaled
capture save "$dir/education/$loopyear.dta", replace
clear all
}

use "$dir/education/2000.dta"

forvalues i= 2009(1)2017 {
global loopyear "`i'"
capture append using "$dir/education/$loopyear.dta"
}
save "$dir/education/Edu.dta", replace

clear all
}
*ARC Expenditure Data
{ 
clear all



import delimited "$dir/projects/ARC2008.csv", clear 
destring arcfunding, replace ignore(",")
destring totalfunding, replace ignore(",")

save "$dir/projects/2008.dta", replace



merge m:m ldd using "$dir/projects/countylist.dta"
drop if _merge==2
drop fipscode_arc
replace fipscode=fips if fipscode==.
drop fips
replace arcfunding=arcfunding*popshare if _merge==3
replace totalfunding=totalfunding*popshare if _merge==3
gen spendinggroup=.
replace spendinggroup=1 if strpos(type, "Sewer") | strpos(type, "Water")
replace spendinggroup=2 if strpos(type, "health") | strpos(type, "Health") ///
 | strpos(type, "hospital")  | strpos(type, "Hospital")  | strpos(type, "medicine") ///
 | strpos(type, "clinic") | strpos(type, "Care") | strpos(type, "care") 
replace spendinggroup=7 if strpos(type, "Education") | strpos(type, "Child") | strpos(type, "child") ///
 | strpos(type, "education") | strpos(type, "school") | strpos(type, "School") | strpos(type, "literacy") ///
 | strpos(type, "Literacy") | strpos(type, "learn") | strpos(type, "Learn") | strpos(type, "STEM") ///
 | strpos(type, "Dropout") | strpos(type, "Workforce") | strpos(type, "Math") | strpos(type, "Teacher")
replace spendinggroup=3 if strpos(type, "touri") | strpos(type, "Touri") ///
 | strpos(type, "Communit")  | strpos(type, "communit") | strpos(type, "Housing") 
 replace spendinggroup=4 if strpos(type, "busin") | strpos(type, "Busines") ///
 | strpos(type, "Indust")  | strpos(type, "indus") | strpos(type, "Downtown") ///
 | strpos(type, "Entrep") 
  replace spendinggroup=5 if strpos(type, "LDD") | strpos(type, "technical") ///
 | strpos(type, "Technical")  | strpos(type, "Admin") | strpos(type, "Revolving") ///
 | strpos(type, "Leader")  | strpos(type, "Plann") | strpos(type, "Research") ///
 | strpos(type, "Conference")
  replace spendinggroup=6 if strpos(type, "Energy") | strpos(type, "Telecom") | strpos(type, "Gas") ///
   | strpos(type, "Waste")
replace spendinggroup=0 if spendinggroup==.
label define spendinggroup 1 "Water & Sewers" 2 "Healthcare" 7 "Education & Training" ///
4 "Business & Industry" 5 "Administrative"  3 "Tourism, Community, Housing" 6 "Non-Water Utilities" ///
0 "Other"
label values spendinggroup spendinggroup
tab type [fw= int(totalfunding)] , sort
tab spendinggroup [fw= int(totalfunding)] , sort

save "$dir/projects/projects_expanded.dta", replace


collapse (sum) arcfunding totalfunding , by(fipscode)


drop if fipscode==.


save "$dir/projects/projects.dta", replace

clear all
}
**Media Salience and News Story Data
{
*Google News
import delimited "$dir/News/news.csv", clear 

gen publicnews=0
replace publicnews=1 if strpos(source,"public") | strpos(source,"Public") 
gen highintensity=0
replace highintensity=max(aboutlabels,map)
gen countystatus=0
replace countystatus=1 if max(distressed,atrisk,transitional,competitive,attainment)>=1
gen distressedonly=0
replace distressedonly=1 if max(atrisk,transitional,competitive,attainment)==0 & distressed>=1
gen distressedmore=0
replace distressedmore=1 if distressed >= max(atrisk,transitional,competitive,attainment)
gen distressmention=0
replace distressmention=1 if distressed>=1
gen othermention=0
replace othermention=1 if max(atrisk,transitional,competitive,attainment)>=1

save "$dir/News/news.dta", replace

clear all

*DMA Collapse
preserve
use "$dir/rank index/rankindex.dta"

keep if fiscalyear<=2009

gen distressed=0
replace distressed=1 if category=="Distressed"

collapse (max) distressed, by(fipscode)
save "$dir/News/DMAcrosswalk_supplement.dta", replace
restore

use "$dir/News/DMAs.dta" 

preserve
keep fipscode DMA
save "$dir/News/DMAcrosswalk.dta", replace
restore

merge 1:1 fipscode using "$dir/News/DMAcrosswalk_supplement.dta"

collapse (sum) distressed,by(DMA)

save "$dir/News/DMAcollapsed.dta", replace

clear all

*Google Trends
import delimited "$dir/News/geoMap.csv", varnames(3) rowrange(3) clear  
rename v2 DMA
merge 1:1 DMA using "$dir/News/DMAcollapsed.dta"
drop if _merge!=3
drop _merge
replace searchrank=(210-searchrank)/210*100

save "$dir/News/DMAcomplete.dta", replace

keep dma-searchrank

save "$dir/News/DMAcharacteristics.dta", replace


clear all
}
*Urbanicity
{
clear all
import delimited "$dir/urbanicity/LND01.csv", clear 
rename stcou fipscode
keep fipscode lnd110200d 
rename lnd110200d sq_miles
save "$dir/urbanicity/landarea.dta", replace
clear all
}
*House Election Data
{
clear

forvalues i=2004(2)2018 {
global year `i'
import excel "$dir/House Data/House_Election_Data_$year.xlsx", sheet("County") cellrange(A3:BG3417) clear
 rename AV fipscode
 rename N dhouse`i'
 rename O rhouse`i'
 duplicates tag fipscode, gen(duplicated)
 tab B if duplicated>=1
 drop if duplicated>=1
 gen house_twoparty_votes_`i' =dhouse`i' +rhouse`i'
 gen dhouse_2party`i' =dhouse`i' /house_twoparty_votes_`i'
 keep fipscode dhouse`i' rhouse`i' house_twoparty_votes_`i' dhouse_2party`i'
 save "$dir/House Data/house$year.dta", replace
}
clear


import excel "$dir/House Data/House_Election_Data_2010.xlsx", sheet("County") cellrange(A3:BG3417) clear
 rename AY fipscode
 rename N dhouse2010
 rename O rhouse2010
 duplicates tag fipscode, gen(duplicated)
 tab B if duplicated>=1
 drop if duplicated>=1
 gen house_twoparty_votes_2010 =dhouse2010 +rhouse2010
 gen dhouse_2party2010 =dhouse2010 /house_twoparty_votes_2010
 keep fipscode dhouse2010 rhouse2010 house_twoparty_votes_2010 dhouse_2party2010
 save "$dir/House Data/house2010.dta", replace
 clear
 
 import excel "$dir/House Data/House_Election_Data_2018.xlsx", sheet("County") cellrange(A3:BG3417) clear
 rename AZ fipscode
 rename N dhouse2018
 rename O rhouse2018
 duplicates tag fipscode, gen(duplicated)
 tab B if duplicated>=1
 drop if duplicated>=1
 gen house_twoparty_votes_2018 =dhouse2018 +rhouse2018
 gen dhouse_2party2018 =dhouse2018 /house_twoparty_votes_2018
 keep fipscode dhouse2018 rhouse2018 house_twoparty_votes_2018 dhouse_2party2018
 save "$dir/House Data/house2018.dta", replace


clear
}
*Federal Expenditure Data
{
clear

{
import excel "$dir/all grants/FederalFunds_2008Data_AL.xls", sheet("FederalFunds_2008Data_AL") firstrow clear

destring FIPSCD_*, replace force

gen population=0
replace population=1 if ProgramDescription=="Population"

keep if ObjectCodeDescription=="Grants" | population==1

collapse (sum) FIPSCD_*, by(population)

reshape long FIPSCD_0, i(population) j(fipscode)

rename FIPSCD_0 spending

reshape wide spending, i(fipscode) j(population)

rename spending0 spending
rename spending1 population

gen federalexp_percap=spending/population
label var federalexp_percap "Sum of Per Capita Federal Expenditures in County"


save "$dir/all grants/AL.dta", replace

clear
}

foreach state in GA KY MD MS NC NY OH PA SC TN VA WV {

global state `state'

import excel "$dir/all grants/FederalFunds_2008Data_$state.xls", sheet("FederalFunds_2008Data_$state") firstrow clear

destring FIPSCD_*, replace force

gen population=0
replace population=1 if ProgramDescription=="Population"

keep if ObjectCodeDescription=="Grants" | population==1

collapse (sum) FIPSCD_*, by(population)

reshape long FIPSCD_, i(population) j(fipscode)

rename FIPSCD_ spending

reshape wide spending, i(fipscode) j(population)

rename spending0 spending
rename spending1 population

gen federalexp_percap=spending/population
label var federalexp_percap "Sum of Per Capita Federal Expenditures in County"


save "$dir/all grants/$state.dta", replace

clear

}

use "$dir/all grants/AL.dta"

foreach state in GA KY MD MS NC NY OH PA SC TN VA WV {

global state `state'

append using "$dir/all grants/$state.dta"
}

save "$dir/all grants/fedexp_percap.dta", replace

clear
}
*Internet Access
{
clear all
import delimited "$dir/internet/hs_countydata_dec_2008.csv"
rename county_fips fipscode
save "$dir/internet/internet2008.dta", replace

}
*Migration
{
clear all


import excel "$dir/migration/county-to-county-2009-2013-ins-outs-nets-gross.xlsx", sheet("Alabama") cellrange(A2:AN8531) firstrow
gen fipscode= StateCodeofGeographyA+FIPSCountyCodeofGeographyA
destring fipscode, replace force
rename FlowfromGeographyBtoGeograp inflow
rename CounterflowfromGeographyAto outflow
rename NetMigrationfromGeographyBt netflow
destring inflow outflow netflow, replace force
drop if netflow==.
collapse (sum) inflow outflow netflow, by(fipscode)

save "$dir/migration/Alabama.dta", replace
clear all


import excel "$dir/migration/county-to-county-2009-2013-ins-outs-nets-gross.xlsx", sheet("Georgia") cellrange(A2:AN20305) firstrow
gen fipscode= StateCodeofGeographyA+FIPSCountyCodeofGeographyA
destring fipscode, replace force
rename FlowfromGeographyBtoGeograp inflow
rename CounterflowfromGeographyAto outflow
rename NetMigrationfromGeographyBt netflow
destring inflow outflow netflow, replace force
drop if netflow==.
collapse (sum) inflow outflow netflow, by(fipscode)

save "$dir/migration/Georgia.dta", replace
clear all

import excel "$dir/migration/county-to-county-2009-2013-ins-outs-nets-gross.xlsx", sheet("Kentucky") cellrange(A2:AN11014) firstrow
gen fipscode= StateCodeofGeographyA+FIPSCountyCodeofGeographyA
destring fipscode, replace force
rename FlowfromGeographyBtoGeograp inflow
rename CounterflowfromGeographyAto outflow
rename NetMigrationfromGeographyBt netflow
destring inflow outflow netflow, replace force
drop if netflow==.
collapse (sum) inflow outflow netflow, by(fipscode)

save "$dir/migration/Kentucky.dta", replace
clear all

import excel "$dir/migration/county-to-county-2009-2013-ins-outs-nets-gross.xlsx", sheet("Maryland") cellrange(A2:AN8504) firstrow
gen fipscode= StateCodeofGeographyA+FIPSCountyCodeofGeographyA
destring fipscode, replace force
rename FlowfromGeographyBtoGeograp inflow
rename CounterflowfromGeographyAto outflow
rename NetMigrationfromGeographyBt netflow
destring inflow outflow netflow, replace force
drop if netflow==.
collapse (sum) inflow outflow netflow, by(fipscode)

save "$dir/migration/Maryland.dta", replace
clear all

import excel "$dir/migration/county-to-county-2009-2013-ins-outs-nets-gross.xlsx", sheet("Mississippi") cellrange(A2:AN8504) firstrow
gen fipscode= StateCodeofGeographyA+FIPSCountyCodeofGeographyA
destring fipscode, replace force
rename FlowfromGeographyBtoGeograp inflow
rename CounterflowfromGeographyAto outflow
rename NetMigrationfromGeographyBt netflow
destring inflow outflow netflow, replace force
drop if netflow==.
collapse (sum) inflow outflow netflow, by(fipscode)

save "$dir/migration/Mississippi.dta", replace
clear all

import excel "$dir/migration/county-to-county-2009-2013-ins-outs-nets-gross.xlsx", sheet("New York") cellrange(A2:AN14792) firstrow
gen fipscode= StateCodeofGeographyA+FIPSCountyCodeofGeographyA
destring fipscode, replace force
rename FlowfromGeographyBtoGeograp inflow
rename CounterflowfromGeographyAto outflow
rename NetMigrationfromGeographyBt netflow
destring inflow outflow netflow, replace force
drop if netflow==.
collapse (sum) inflow outflow netflow, by(fipscode)

save "$dir/migration/NewYork.dta", replace
clear all

import excel "$dir/migration/county-to-county-2009-2013-ins-outs-nets-gross.xlsx", sheet("North Carolina") cellrange(A2:AN17535) firstrow
gen fipscode= StateCodeofGeographyA+FIPSCountyCodeofGeographyA
destring fipscode, replace force
rename FlowfromGeographyBtoGeograp inflow
rename CounterflowfromGeographyAto outflow
rename NetMigrationfromGeographyBt netflow
destring inflow outflow netflow, replace force
drop if netflow==.
collapse (sum) inflow outflow netflow, by(fipscode)

save "$dir/migration/NorthCarolina.dta", replace
clear all

import excel "$dir/migration/county-to-county-2009-2013-ins-outs-nets-gross.xlsx", sheet("Ohio") cellrange(A2:AN14923) firstrow
gen fipscode= StateCodeofGeographyA+FIPSCountyCodeofGeographyA
destring fipscode, replace force
rename FlowfromGeographyBtoGeograp inflow
rename CounterflowfromGeographyAto outflow
rename NetMigrationfromGeographyBt netflow
destring inflow outflow netflow, replace force
drop if netflow==.
collapse (sum) inflow outflow netflow, by(fipscode)

save "$dir/migration/Ohio.dta", replace
clear all

import excel "$dir/migration/county-to-county-2009-2013-ins-outs-nets-gross.xlsx", sheet("Pennsylvania") cellrange(A2:AN14225) firstrow
gen fipscode= StateCodeofGeographyA+FIPSCountyCodeofGeographyA
destring fipscode, replace force
rename FlowfromGeographyBtoGeograp inflow
rename CounterflowfromGeographyAto outflow
rename NetMigrationfromGeographyBt netflow
destring inflow outflow netflow, replace force
drop if netflow==.
collapse (sum) inflow outflow netflow, by(fipscode)

save "$dir/migration/Pennsylvania.dta", replace
clear all

import excel "$dir/migration/county-to-county-2009-2013-ins-outs-nets-gross.xlsx", sheet("South Carolina") cellrange(A2:AN8505) firstrow
gen fipscode= StateCodeofGeographyA+FIPSCountyCodeofGeographyA
destring fipscode, replace force
rename FlowfromGeographyBtoGeograp inflow
rename CounterflowfromGeographyAto outflow
rename NetMigrationfromGeographyBt netflow
destring inflow outflow netflow, replace force
drop if netflow==.
collapse (sum) inflow outflow netflow, by(fipscode)

save "$dir/migration/SouthCarolina.dta", replace
clear all

import excel "$dir/migration/county-to-county-2009-2013-ins-outs-nets-gross.xlsx", sheet("Tennessee") cellrange(A2:AN11855) firstrow
gen fipscode= StateCodeofGeographyA+FIPSCountyCodeofGeographyA
destring fipscode, replace force
rename FlowfromGeographyBtoGeograp inflow
rename CounterflowfromGeographyAto outflow
rename NetMigrationfromGeographyBt netflow
destring inflow outflow netflow, replace force
drop if netflow==.
collapse (sum) inflow outflow netflow, by(fipscode)

save "$dir/migration/Tennessee.dta", replace
clear all

import excel "$dir/migration/county-to-county-2009-2013-ins-outs-nets-gross.xlsx", sheet("Virginia") cellrange(A2:AN18435) firstrow
gen fipscode= StateCodeofGeographyA+FIPSCountyCodeofGeographyA
destring fipscode, replace force
rename FlowfromGeographyBtoGeograp inflow
rename CounterflowfromGeographyAto outflow
rename NetMigrationfromGeographyBt netflow
destring inflow outflow netflow, replace force
drop if netflow==.
collapse (sum) inflow outflow netflow, by(fipscode)

save "$dir/migration/Virginia.dta", replace
clear all

import excel "$dir/migration/county-to-county-2009-2013-ins-outs-nets-gross.xlsx", sheet("West Virginia") cellrange(A2:AN8504) firstrow
gen fipscode= StateCodeofGeographyA+FIPSCountyCodeofGeographyA
destring fipscode, replace force
rename FlowfromGeographyBtoGeograp inflow
rename CounterflowfromGeographyAto outflow
rename NetMigrationfromGeographyBt netflow
destring inflow outflow netflow, replace force
drop if netflow==.
collapse (sum) inflow outflow netflow, by(fipscode)

save "$dir/migration/WestVirginia.dta", replace
clear all

use "$dir/migration/Alabama.dta", clear
append using "$dir/migration/Georgia.dta"
append using "$dir/migration/Kentucky.dta"
append using "$dir/migration/Maryland.dta"
append using "$dir/migration/Mississippi.dta"
append using "$dir/migration/NewYork.dta"
append using "$dir/migration/NorthCarolina.dta"
append using "$dir/migration/Ohio.dta"
append using "$dir/migration/Pennsylvania.dta"
append using "$dir/migration/SouthCarolina.dta"
append using "$dir/migration/Tennessee.dta"
append using "$dir/migration/Virginia.dta"
append using "$dir/migration/WestVirginia.dta"

save "$dir/migration/migration.dta", replace

clear all

}
*Intermediate Data Cleanup
{
*Intermediate Rank Index
clear all

use "$dir/rank index/rankindex.dta"

keep if (fiscalyear>=2007 & fiscalyear<=2009 ) | fiscalyear==2011 | fiscalyear==2013 ///
| fiscalyear==2015 | fiscalyear==2017 | fiscalyear==2019 

encode State, gen(state)
encode category, gen(countylabel)

gen fipscode_arc=fipscode

expand 2 if (fipscode==51163 | fipscode== 51005 | fipscode==51035 | fipscode== 51121 | fipscode== 51191 | ///
fipscode== 51195 | fipscode== 51089), gen(va_expansion)

expand 2 if (fipscode==51163 & va_expansion==1), gen(va_expansion2)

replace County= "Alleghany" if fipscode== 51005 & va_expansion==0
replace County= "Covington City" if fipscode== 51005 & va_expansion==1
replace fipscode=51580 if fipscode== 51005 & va_expansion==1

replace County= "Carroll" if fipscode== 51035 & va_expansion==0
replace County= "Galax City" if fipscode== 51035 & va_expansion==1
replace fipscode=51640 if fipscode== 51035 & va_expansion==1

replace County= "Montgomery" if fipscode== 51121 & va_expansion==0
replace County= "Radford City" if fipscode== 51121 & va_expansion==1
replace fipscode=51750 if fipscode== 51121 & va_expansion==1

replace County= "Washington" if fipscode== 51191 & va_expansion==0
replace County= "Bristol City" if fipscode== 51191 & va_expansion==1
replace fipscode=51520 if fipscode== 51191 & va_expansion==1

replace County= "Wise" if fipscode== 51195 & va_expansion==0
replace County= "Norton City" if fipscode== 51195 & va_expansion==1
replace fipscode=51720 if fipscode== 51195 & va_expansion==1

replace County= "Henry" if fipscode== 51089 & va_expansion==0
replace County= "Martinsville City" if fipscode== 51089 & va_expansion==1
replace fipscode=51690 if fipscode== 51089 & va_expansion==1

replace County= "Rockbridge" if fipscode== 51163 & va_expansion==0
replace County= "Buena Vista City" if fipscode== 51163 & va_expansion==1 & va_expansion2==0
replace fipscode=51530 if fipscode== 51163 & va_expansion==1 & va_expansion2==0
replace County= "Lexington City" if fipscode== 51163 & va_expansion==1 & va_expansion2==1
replace fipscode=51678 if fipscode== 51163 & va_expansion==1 & va_expansion2==1

drop category va_expansion va_expansion2

replace County= "DeKalb" if County=="De Kalb"

reshape wide urate-rankindex countylabel, i(fipscode) j(fiscalyear)

save "$dir/rank index/rankindex_intermediate.dta", replace

clear all

*Intermediate Industry Data
use "$dir/all industries/allindustries.dta"

keep if qtr==4 & month==1 & (year==2006 | year==2008 | year==2010 | year==2012 | year==2014 | year==2016 | year==2018 ) 
keep fipscode fiscalyear jobs0 jobs1 jobs2 jobs3 jobs5 jobs8 year
rename jobs0 totaljobs
rename jobs1 fedgov_jobs
rename jobs2 stategov_jobs
rename jobs3 localgov_jobs
rename jobs5 private_jobs
rename jobs8 gov_jobs 

preserve
keep if year==2006
rename totaljobs totaljobs2006
rename fedgov_jobs fedgov_jobs2006
rename stategov_jobs stategov_jobs2006
rename localgov_jobs localgov_jobs2006
rename private_jobs private_jobs2006
rename gov_jobs gov_jobs2006
drop year fiscal year
save "$dir/all industries/allindustries_intermediate1.dta", replace
restore

keep if year!=2006
rename year electionyear
drop fiscalyear
save "$dir/all industries/allindustries_intermediate2.dta", replace


clear all

*Intermediate Census Data
use "$dir/censusdata/WorkingAgePop.dta"

keep if (year==2006 | year==2008 | year==2010 | year==2012 | year==2014 | year==2016 | year==2018 ) 

preserve
keep if year==2006
drop year
rename working_age_pop working_age_pop2006
save "$dir/censusdata/WorkingAgePop_intermediate1.dta", replace
restore

keep if year!=2006
rename year electionyear
save "$dir/censusdata/WorkingAgePop_intermediate2.dta", replace

clear all



*Intermediate Election Data
use "$dir/election data/presidential_elections.dta"

preserve
drop totalvotes
reshape wide democrat-d_2party, i(fipscode) j(electionyear)
save "$dir/election data/presidential_intermediate1.dta", replace
restore

drop totalvotes
keep if electionyear>=2008
save "$dir/election data/presidential_intermediate2.dta", replace

clear all

*Voting Age Population Data
use "$dir/censusdata/2000-2018.dta"

keep if year == 2004 | year == 2008 | year == 2010 | year == 2012 | year == 2014 ///
| year == 2016 | year == 2018

keep adult_pop fipscode year stname ctyname

reshape wide adult_pop, i(fipscode) j(year)

save "$dir/censusdata/AdultPop.dta", replace

clear all

* Over 65 Years of ageAge 

use "$dir/censusdata/WorkingAgePop.dta"

merge 1:1 fipscode year using "$dir/censusdata/2000-2018.dta"

gen over65= (adult_pop-working_age_pop)/adult_pop
label var over65 "Share of VAP Over 65 in 2004"
keep if year==2004
keep fipscode over65
save "$dir/censusdata/over65.dta", replace
clear all
}
*Merge Data
{
clear all

use "$dir/rank index/rankindex_intermediate.dta"

merge 1:1 fipscode using "$dir/all industries/allindustries_intermediate1.dta"
drop if _merge!=3
drop _merge

merge 1:1 fipscode using "$dir/censusdata/WorkingAgePop_intermediate1.dta"
drop if _merge!=3
drop _merge



gen edyear=2000
rename fipscode fipscode_temp
rename fipscode_arc fipscode
merge m:1 fipscode edyear using "$dir/education/Edu.dta"
drop if _merge!=3
drop _merge
rename fipscode fipscode_arc
rename fipscode_temp fipscode

merge 1:1 fipscode using "$dir/election data/presidential_intermediate1.dta"
drop if _merge!=3
drop _merge

merge 1:1 fipscode using "$dir/urbanicity/landarea.dta"
drop if _merge==2
drop _merge

merge 1:1 fipscode using "$dir/projects/projects.dta"
drop if _merge==2
replace arcfunding=0 if arcfunding==.
replace totalfunding=0 if totalfunding==.
rename _merge fundingmerge

rename fipscode fipscode_temp
rename fipscode_arc fipscode
merge m:1 fipscode using "$dir/News/DMAcrosswalk.dta"
drop _merge
rename fipscode fipscode_arc
rename fipscode_temp fipscode

merge m:1 DMA using "$dir/News/DMAcharacteristics.dta"
drop _merge

forvalues i=2004(2)2018 {
global year `i'
merge 1:1 fipscode using "$dir/House Data/house$year.dta"
drop if _merge==2
drop _merge
}

order fipscode state County
sort fipscode

expand 2, gen(electionyear_temp1)
expand 2 if electionyear_temp1==1, gen(electionyear_temp2)
expand 2 if electionyear_temp2==1, gen(electionyear_temp3)
expand 2 if electionyear_temp3==1, gen(electionyear_temp4)
expand 2 if electionyear_temp4==1, gen(electionyear_temp5)


gen electionyear=2008+2*electionyear_temp1+2*electionyear_temp2 +2*electionyear_temp3 ///
+2*electionyear_temp4 +2*electionyear_temp5 
drop electionyear_temp1-electionyear_temp5

merge 1:1 fipscode electionyear using "$dir/all industries/allindustries_intermediate2.dta"
drop if _merge==2
drop _merge

merge 1:1 fipscode electionyear using "$dir/censusdata/WorkingAgePop_intermediate2.dta"
drop if _merge==2
drop _merge

merge 1:1 fipscode electionyear using "$dir/election data/presidential_intermediate2.dta"
drop if _merge==2
drop _merge

merge m:1 fipscode using "$dir/censusdata/AdultPop.dta"
drop if _merge==2
drop _merge

merge m:1 fipscode using "$dir/censusdata/over65.dta"
drop if _merge==2
drop _merge

merge m:1 fipscode electionyear using "$dir/SNAP/SNAP.dta"
drop if _merge==2
drop _merge

merge m:1 fipscode using "$dir/all grants/fedexp_percap.dta"
drop if _merge==2
drop _merge

merge m:1 fipscode using "$dir/internet/internet2008.dta"
drop if _merge==2
drop _merge

merge m:1 fipscode using "$dir/News/FCCbroadcastDTV.dta", force
drop if _merge==2
drop _merge

merge m:1 fipscode using "$dir/elevation/elevation.dta", force
drop if _merge==2
drop _merge

merge m:1 fipscode using "$dir/election data/presidential_elections1996.dta", force
drop if _merge==2
drop _merge

merge m:1 fipscode using "$dir/migration/migration.dta", force
drop if _merge==2
drop _merge

}
*Generate New Variables
{


gen dhouse_2party=.
forvalues i=2008(2)2018 {
replace dhouse_2party= dhouse_2party`i' if electionyear==`i'
}

gen adult_pop=adult_pop2008
gen erate2006=totaljobs2006/working_age_pop2006
gen erate = totaljobs/working_age_pop
gen statefips=int(fipscode/1000)
gen density = adult_pop/sq_miles


forvalues i=2007(1)2009{
gen rindex`i'=rankindex`i'
replace rindex`i'=rankindex`i'/3104*100


gen normrindex`i'=rindex`i'-90

gen generosity`i'=0
replace generosity`i'=.80 if rindex`i'>=(90)
replace generosity`i'=.70 if rindex`i'<(90) & rindex`i'>=(75)
replace generosity`i'=.50 if rindex`i'<(75) & rindex`i'>=(25)
replace generosity`i'=.30 if rindex`i'<(25) & rindex`i'>=(10)

gen LDDgenerosity`i'=.50
replace LDDgenerosity`i'=.75 if rindex`i'>=(90)
replace LDDgenerosity`i'=.70 if rindex`i'<(90) & rindex`i'>=(75)
replace LDDgenerosity`i'=.50 if rindex`i'<(75) & rindex`i'>=(25)
replace LDDgenerosity`i'=.50 if rindex`i'<(25) & rindex`i'>=(10)

gen ADHSgenerosity`i'=.80
replace ADHSgenerosity`i'=.80 if rindex`i'>=(90)
replace ADHSgenerosity`i'=.80 if rindex`i'<(90) & rindex`i'>=(75)
replace ADHSgenerosity`i'=.80 if rindex`i'<(75) & rindex`i'>=(25)
replace ADHSgenerosity`i'=.80 if rindex`i'<(25) & rindex`i'>=(10)

gen Roadgenerosity`i'=.30
replace Roadgenerosity`i'=.80 if rindex`i'>=(90)
replace Roadgenerosity`i'=.80 if rindex`i'<(90) & rindex`i'>=(75)
replace Roadgenerosity`i'=.80 if rindex`i'<(75) & rindex`i'>=(25)
replace Roadgenerosity`i'=.30 if rindex`i'<(25) & rindex`i'>=(10)

gen countygroup`i'=1
replace countygroup`i'=5 if rindex`i'>=(90)
replace countygroup`i'=4 if rindex`i'<(90) & rindex`i'>=(75)
replace countygroup`i'=3 if rindex`i'<(75) & rindex`i'>=(25)
replace countygroup`i'=2 if rindex`i'<(25) & rindex`i'>=(10)
capture label define countygroup 1 "Attainment" 2 "Competitive" 3 "Transitional" 4 "At-risk" 5 "Distressed"
label values countygroup`i' countygroup

generate attainment`i'=0
replace attainment`i'=1 if countygroup`i'==1
generate competitive`i'=0
replace competitive`i'=1 if countygroup`i'==2
generate transitional`i'=0
replace transitional`i'=1 if countygroup`i'==3
generate atrisk`i'=0
replace atrisk`i'=1 if countygroup`i'==4
generate distressed`i'=0
replace distressed`i'=1 if countygroup`i'==5
}


forvalues i=2011(2)2019{
gen rindex`i'=rankindex`i'
replace rindex`i'=rankindex`i'/3104*100


gen normrindex`i'=rindex`i'-90

gen generosity`i'=0
replace generosity`i'=.80 if rindex`i'>=(90)
replace generosity`i'=.70 if rindex`i'<(90) & rindex`i'>=(75)
replace generosity`i'=.50 if rindex`i'<(75) & rindex`i'>=(25)
replace generosity`i'=.30 if rindex`i'<(25) & rindex`i'>=(10)

gen LDDgenerosity`i'=.50
replace LDDgenerosity`i'=.75 if rindex`i'>=(90)
replace LDDgenerosity`i'=.70 if rindex`i'<(90) & rindex`i'>=(75)
replace LDDgenerosity`i'=.50 if rindex`i'<(75) & rindex`i'>=(25)
replace LDDgenerosity`i'=.50 if rindex`i'<(25) & rindex`i'>=(10)

gen ADHSgenerosity`i'=.80
replace ADHSgenerosity`i'=.80 if rindex`i'>=(90)
replace ADHSgenerosity`i'=.80 if rindex`i'<(90) & rindex`i'>=(75)
replace ADHSgenerosity`i'=.80 if rindex`i'<(75) & rindex`i'>=(25)
replace ADHSgenerosity`i'=.80 if rindex`i'<(25) & rindex`i'>=(10)

gen Roadgenerosity`i'=.30
replace Roadgenerosity`i'=.80 if rindex`i'>=(90)
replace Roadgenerosity`i'=.80 if rindex`i'<(90) & rindex`i'>=(75)
replace Roadgenerosity`i'=.80 if rindex`i'<(75) & rindex`i'>=(25)
replace Roadgenerosity`i'=.30 if rindex`i'<(25) & rindex`i'>=(10)

gen countygroup`i'=1
replace countygroup`i'=5 if rindex`i'>=(90)
replace countygroup`i'=4 if rindex`i'<(90) & rindex`i'>=(75)
replace countygroup`i'=3 if rindex`i'<(75) & rindex`i'>=(25)
replace countygroup`i'=2 if rindex`i'<(25) & rindex`i'>=(10)
capture label define countygroup 1 "Attainment" 2 "Competitive" 3 "Transitional" 4 "At-risk" 5 "Distressed"
label values countygroup`i' countygroup

generate attainment`i'=0
replace attainment`i'=1 if countygroup`i'==1
generate competitive`i'=0
replace competitive`i'=1 if countygroup`i'==2
generate transitional`i'=0
replace transitional`i'=1 if countygroup`i'==3
generate atrisk`i'=0
replace atrisk`i'=1 if countygroup`i'==4
generate distressed`i'=0
replace distressed`i'=1 if countygroup`i'==5
}


 tab countylabel2007 countygroup2007
tab countylabel2009 countygroup2009
tab countylabel2008 countygroup2008

gen totfunding_percap=totalfunding/adult_pop
gen anyfunding=arcfunding
replace anyfunding=1 if anyfunding>0
gen truematch=arcfunding/totalfunding
drop arcfunding


gen countygroup5=distressed2009
gen normrindex=normrindex2009
gen rindex=rindex2009
gen urate_scaled=urate_scaled2009
gen prate_scaled=prate_scaled2009
gen income_scaled=income_scaled2009


gen index2009=compositeindex2009-169.95
gen index2011=compositeindex2011-171.95
gen index2013=compositeindex2013-160.35
gen index2015=compositeindex2015-158.25
gen index2017=compositeindex2017-161.45
gen index2019=compositeindex2019-167.4

gen index=index2009 if electionyear==2008
replace index=index2011 if electionyear==2010
replace index=index2013 if electionyear==2012
replace index=index2015 if electionyear==2014
replace index=index2017 if electionyear==2016
replace index=index2019 if electionyear==2018

generate distressed=0
replace distressed=1 if index>=0


gen tag2009=0
replace tag2009=1 if index2009>=0
gen tag2011=0
replace tag2011=1 if index2011>=0
gen tag2013=0
replace tag2013=1 if index2013>=0
gen tag2015=0
replace tag2015=1 if index2015>=0
gen tag2017=0
replace tag2017=1 if index2017>=0
gen tag2019=0
replace tag2019=1 if index2019>=0

gen pshift=d_2party-d_2party2004
gen hshift=dhouse_2party-dhouse_2party2004


gen everindex=index2009 if electionyear==2008
replace everindex=max(index2009,index2011) if electionyear==2010
replace everindex=max(index2009,index2011,index2013) if electionyear==2012
replace everindex=max(index2009,index2011,index2013,index2015) if electionyear==2014
replace everindex=max(index2009,index2011,index2013,index2015,index2017) if electionyear==2016
replace everindex=max(index2009,index2011,index2013,index2015,index2017,index2019) if electionyear==2018

gen everdistressed=0
replace everdistressed=1 if everindex>=0




pca ElevFt total_prov DTVsignal
predict media, score
egen stddev= sd(media)
replace media=media/stddev
drop stddev

pca total_prov DTVsignal
predict access, score
egen stddev= sd(access)
replace access=access/stddev
drop stddev

gen tippingpoint538=0
replace tippingpoint538=52*100/(52+50+40+28+25+8+5+3+2+1+1) if State=="Virginia" & electionyear==2008
replace tippingpoint538=3*100/(52+50+40+28+25+8+5+3+2+1+1) if State=="North Carolina" & electionyear==2008
replace tippingpoint538=25*100/(52+50+40+28+25+8+5+3+2+1+1) if State=="Ohio" & electionyear==2008
replace tippingpoint538=28*100/(52+50+40+28+25+8+5+3+2+1+1) if State=="Pennsylvania" & electionyear==2008
replace tippingpoint538=32 if State=="Ohio" & electionyear==2012
replace tippingpoint538=9 if State=="Virginia" & electionyear==2012
replace tippingpoint538=5 if State=="Pennsylvania" & electionyear==2012
replace tippingpoint538=17.6 if State=="Pennsylvania" & electionyear==2016
replace tippingpoint538=11.2 if State=="North Carolina" & electionyear==2016
replace tippingpoint538=6 if State=="Virginia" & electionyear==2016
replace tippingpoint538=5.2 if State=="Ohio" & electionyear==2016
replace tippingpoint538=2.3 if State=="Georgia" & electionyear==2016
replace tippingpoint538=0.3 if State=="South Carolina" & electionyear==2016

replace tippingpoint538=tippingpoint538/100


*Label Variables and Finalize Dataset
{
gen futtag=(tag2011+tag2013+tag2015+tag2017+tag2019)/5

keep fipscode urate2009-countylabel2009 futtag tag2011 State fipscode_arc stname ctyname hsgradrate-coll_scaled d_2party2000 d_2party2004 ///
d_2party2008 d_2party2012 d_2party2016 DMA-arcsearch dhouse_2party2004 dhouse_2party2008 dhouse_2party2010 dhouse_2party2012 dhouse_2party2014 ///
dhouse_2party2016 dhouse_2party2018 electionyear d_2party over65 fns_waived federalexp_percap statename-countyname total_prov DTVsignal ///
ElevFt d_2party1996 netflow dhouse_2party erate2006 erate density rindex2009-distressed2009 totfunding_percap truematch  ///
 index2009 index distressed tag2009 pshift media tippingpoint538  state County democrat* republican*
 drop democrat republican

label var urate2009 "Lagged Unemployment Rate used in FY09"
label var income2009 "Lagged Per Capita Market Income used in FY09"
label var prate2009 "Lagged Unemployment Rate used in FY09"
label var urate_scaled2009 "Lagged Scaled Unemployment Rate used in FY09"
label var income_scaled2009 "Lagged Scaled Per Capita Market Income used in FY09"
label var prate_scaled2009 "Lagged Scaled Unemployment Rate used in FY09"
label var compositeindex2009 "Composite Rescaled Index in FY09"
label var rankindex2009 "Composite Rescaled County Rank in FY09"
label var countylabel2009 "County Label for FY09 (Announced Summer 08)"
label var fipscode_arc "ARC-Designated FIPS Crosswalk"
label var stname "State Name"
label var ctyname "County Name"

forvalues i= 2000(4)2016 {
global loopyear "`i'"
label var democrat`i' "Democratic Presidential Votes `i'"
label var republican`i' "Republican Presidential Votes `i'"
label var d_2party`i' "Democratic Presidential 2-Party Vote `i'"
}

label var DMA "Nielsen DMA Code"
label var arcsearch "ARC Awareness Index"
label var dhouse_2party2004 "Democratic House 2-Party Vote 2004"

forvalues i= 2008(2)2018 {
global loopyear "`i'"
label var dhouse_2party`i' "Democratic House 2-Party Vote `i'"
}

label var electionyear "Election Year of erate, d_2party, and dhouse_2party"
label var d_2party "Democratic Presidential 2-Party Vote in (electionyear)"
label var over65 "Share of Adults over 65 in 2006"
label var fns_waived "County Waiver from SNAP ABAWD Requirements"
label var federalexp_percap "Total Federal Grant Spending per Capita"
label var total_prov "Total ISPs in County in 2008"
label var DTVsignal "Number of Strong and Moderate Signal DTV Channels"
label var ElevFt "Maximum Mountain Peak Elevation in Feet"
label var d_2party1996 "Democratic Presidential 2-Party Vote in 1996"
label var netflow "Net Migration to County 2007-2013"
label var dhouse_2party "Democratic House 2-Party Vote in (electionyear)"
label var erate2006 "County Adult Employment Rate in 2006"
label var erate "County Adult Employment Rate in 2006"
label var density "Population Density"
label var rindex2009 "Approximate Percentile Rank of County"
label var normrindex2009 "Approximate Normalized Percentile Rank of County"
label var generosity2009 "General Grant Matching Rate"
label var LDDgenerosity2009 "LDD Grant Matching Rate"
label var ADHSgenerosity2009 "ADHS Grant Matching Rate"
label var Roadgenerosity2009 "Rural Road Grant Matching Rate"
label var countygroup2009 "County Label for FY09 (Announced Summer 08)"
label var attainment2009 "County is Attainment for FY09"
label var competitive2009 "County is Competitive for FY09"
label var transitional2009 "County is Transitional for FY09"
label var atrisk2009 "County is At-Risk for FY09"
label var distressed2009 "County is Distressed for FY09"
label var totfunding_percap "Total ARC Funding per Capita in 2008"
label var truematch "True ARC Match Rate in 2008"
label var index2009 "Normalized Composite Score Value for FY09"
label var index "Normalized Composite Index Score for FY announced in (electionyear)"
label var distressed "County is Distressed for FY announced in (electionyear)"
label var tag2009 "County Normalized Composite Score Value for FY09 is >0"
label var tag2011 "County Normalized Composite Score Value for FY11 is >0"
label var pshift "2-Party Democratic Presidential Vote Shift 04-08"
label var media "Media Penetration Index"
label var tippingpoint538 "538 Tipping Point Probability in (electionyear)"
label var futtag "Total Distressed Ratings in Election Years"

}



save "$dir/readydata/REStat_Firoozi22.dta", replace
}




clear all

log close


